package com.miao.util.excel;

import com.miao.util.file.FileUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author Created by Miaosk
 * @date Created on 2015/5/15.
 */
public class excelUtils {

    public static void main(String[] args) throws Exception {
        /*
        导出
         */
        List<Map> list = new ArrayList<Map>();
        for (int i = 0; i < 10; i++) {
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("userName", "zhang" + i);
            map.put("loginName", i);
            list.add(map);
        }
        String[] headers = new String[]{"姓名", "帐号"};
        byte[] bytes = excelUtils.listToByteDatas(list, headers);
        FileUtil.toFile("E:\\test.xls", bytes);
        System.out.println("导出成功");
        /*
        导入
         */
        String fileName = "E:\\test.xls";
        InputStream inputStream = new FileInputStream(fileName);
        List<String[]> result = excelUtils.getExcelData(inputStream, fileName);
        System.out.println(result.size());

    }

    public static byte[] listToByteDatas(List<Map> list, String[] headers) throws Exception {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        Workbook wb = new HSSFWorkbook();
        Object[] keys = list.get(0).keySet().toArray();
        int length = list.size();
        Sheet s = wb.createSheet();
        Row r = null;
        Cell c = null;
        Row head = s.createRow(0);
        //设置标题
        for (int num = 0; num < headers.length; num++) {
            c = head.createCell(num);
            c.setCellValue(headers[num]);
        }
        //填充内容
        for (int i = 1; i <= length; i++) {
            r = s.createRow(i);
            for (int num = 0; num < headers.length; num++) {
                c = r.createCell(num);
                c.setCellValue(list.get(i - 1).get(keys[num]).toString());
            }
        }
        wb.createSheet();
        wb.write(os);
        byte[] data = os.toByteArray();
        return data;
    }

    //判断excel版本
    static Workbook openWorkbook(InputStream in, String filename) throws IOException {
        Workbook wb = null;
        if (filename.endsWith(".xlsx")) {
            wb = new XSSFWorkbook(in);//Excel 2007
        } else {
            wb = new HSSFWorkbook(in);//Excel 2003
        }
        return wb;
    }

    public static Workbook openWorkbook(byte[] data) {
        Workbook wb = null;
        try {
            InputStream ins = new ByteArrayInputStream(data);
            wb = new XSSFWorkbook(ins);//Excel 2007+
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        if (wb == null) {
            try {
                InputStream ins = new ByteArrayInputStream(data);
                wb = new HSSFWorkbook(ins);//Excel 2003
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return wb;
    }

    public static List<String[]> getExcelData(InputStream inputStream, String fileName) throws Exception {
        Workbook wb = openWorkbook(inputStream, fileName);
        return getExcelData(wb);
    }

    public static List<String[]> getExcelData(String filePath) throws Exception {
        InputStream in = new FileInputStream(filePath);
        Workbook wb = openWorkbook(in, filePath);
        return getExcelData(wb);
    }

    public static List<String[]> getExcelData(Workbook wb) throws Exception {
        Sheet sheet = (Sheet) wb.getSheetAt(0);
        Row row = null;
        Cell cell = null;

        int totalRows = sheet.getPhysicalNumberOfRows();
        int totalCells = 0;
        for (int r = 0; r < totalRows; r++) {
            int cell_num = sheet.getRow(r).getLastCellNum();
            if (cell_num > totalCells) {
                totalCells = cell_num;
            }
        }
//        System.out.println("有效列："+totalCells);
        List<String[]> list = new ArrayList<String[]>();
//        String[] menue = new String[]{"dept", "emial", "name", "sex", "birthday", "entryDate", "jobName", "leader", "personApproving", "tel", "mobile", "summary", "explain"};
        for (int r = 0; r < totalRows; r++) {
//            Map map = new HashMap();
            row = sheet.getRow(r);
            String[] row_data = new String[totalCells];
            for (int c = 0; c < totalCells; c++) {
                cell = row.getCell(c);
                String cellValue = "";
                if (null != cell) {
                    if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                        cellValue = DecimalFormat.getInstance().format(cell.getNumericCellValue()).toString().replace(",", "");
                        //时间格式
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            Date dd = cell.getDateCellValue();
                            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                            cellValue = df.format(dd);
                        }
                    } else {
                        cellValue = cell.getStringCellValue();
                    }
                }

                row_data[c] = cellValue;
            }
            list.add(row_data);
        }
        return list;
    }
}
